package org.jgs1904.apachepoidemo.utils;

import lombok.extern.slf4j.Slf4j;
import org.apache.commons.compress.utils.IOUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.imageio.ImageIO;
import java.awt.image.BufferedImage;
import java.io.*;
import java.net.URL;
import java.net.URLConnection;
import java.net.URLEncoder;

/**
 * 使用 Apache POI 写入 Excel 单元格中的图片
 *
 * @author: 默苍璃
 * @date: 2023-10-10 16:04
 */
@Slf4j
public class ExcelImageWriter {

    public static void main(String[] args) throws IOException {
//        XSSFWorkbook wb = new XSSFWorkbook();  // or new XSSFWorkbook();
//        XSSFSheet sheet1 = (XSSFSheet) wb.createSheet("new sheet");
//        String fileUrl = "https://cn.bing.com/images/search?view=detailV2&ccid=DSO2qaoz&id=553AB21BB3A03AB84DE8DBF3C93E9ADF5ADA2396&thid=OIP.DSO2qaozX0tibWbN73gb5gHaE_&mediaurl=https%3a%2f%2fimg.phb123.com%2fuploads%2fallimg%2f190612%2f25-1Z6121F9340-L.png&exph=465&expw=690&q=%e5%9b%be%e7%89%87+%e5%8f%af%e7%88%b1&simid=607994149340651037&FORM=IRPRST&ck=5977A4D276D9027E4E05F3744E05FB23&selectedIndex=67";
////        String fileUrl = "https://cn.bing.com/images/search?view=detailV2&ccid=4vESsK8L&id=64673AA78BF7CC841D3CBE2617B75C727277E12A&thid=OIP.4vESsK8Lgunzlm-5wZL5QAHaI6&mediaurl=https%3a%2f%2fts1.cn.mm.bing.net%2fth%2fid%2fR-C.e2f112b0af0b82e9f3966fb9c192f940%3frik%3dKuF3cnJctxcmvg%26riu%3dhttp%253a%252f%252fwww.kuaipng.com%252fUploads%252fpic%252fwater%252f42535%252fgoods_water_42535_698_840.82_.png%26ehk%3dEafkobXFzSam0egvDa2SmNZ6WuR%252f69%252fsqiipmlmQyE8%253d%26risl%3d%26pid%3dImgRaw%26r%3d0&exph=840&expw=698&q=%e5%9b%be%e7%89%87+%e5%8f%af%e7%88%b1++png&simid=608052960354641005&FORM=IRPRST&ck=2BCF4F904DF657B8E212092963E3D9EB&selectedIndex=1";
//        int row = 0;
//        int col = 0;
//        double scaleX = 1.0;
//        double scaleY = 1.0;

        String fileType = "";

//        picture2(wb, sheet1, fileUrl, row, col, scaleX, scaleY);
//
////        String fileUrl, String fileType, int row, int col
//        picture(wb, sheet1, fileUrl, fileType, row, col);
        picture2();
//        picture3();
    }

    /**
     * @param col    第多少列
     * @param scaleX X轴
     * @param scaleY Y轴
     * @Title: picture2
     * @Description: 方法一：图片插入至单元格
     * @author: zxfeng
     * @date 2021年3月9日 下午2:25:30
     * @version
     */
    public static void picture2() {
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet1 = (XSSFSheet) workbook.createSheet("new sheet");

        String fileUrl = "https://ts3.cn.mm.bing.net/th?id=OIP-C.ZkoPhpKfJwsvGmpm8RsragHaFp&w=286&h=218&c=8&rs=1&qlt=90&o=6&pid=3.1&rm=2";

        try {
//            //防止URL地址有中文，解码
//            String head = fileUrl.substring(0, fileUrl.lastIndexOf("/") + 1);
//            String suffix = fileUrl.substring(fileUrl.lastIndexOf("/") + 1);
//            String link = head + URLEncoder.encode(suffix, "UTF-8");
//            log.info(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>拼接URL为：[{}]", link);
            // 构造URL
            URL url = new URL(fileUrl);
            // 打开连接
            URLConnection con = url.openConnection();
            //设置请求超时为8s
            con.setConnectTimeout(8 * 1000);
            // 输入流
            InputStream is = con.getInputStream();
            byte[] bytes = IOUtils.toByteArray(is);
//            @SuppressWarnings("static-access") int pictureIdx = workbook.addPicture(bytes, workbook.PICTURE_TYPE_PNG);
            @SuppressWarnings("static-access") int pictureIdx = workbook.addPicture(bytes, workbook.PICTURE_TYPE_JPEG);
            CreationHelper helper = workbook.getCreationHelper();
            Drawing drawing = sheet1.createDrawingPatriarch();
            ClientAnchor anchor = helper.createClientAnchor();
            // 图片插入坐标
            anchor.setCol1(0);
            anchor.setRow1(0);
            // 插入图片
            Picture pict = drawing.createPicture(anchor, pictureIdx);
            //pict.resize(scaleX, scaleY) 方法用于设置插入的图片的大小，其中 scaleX 和 scaleY 是缩放比例。
            pict.resize(1, 1);

            try (OutputStream fileOut = new FileOutputStream("D:\\temp\\ExcelImageWriter.xlsx")) {
                workbook.write(fileOut);
            } catch (FileNotFoundException e) {
                throw new RuntimeException(e);
            } catch (IOException e) {
                throw new RuntimeException(e);
            }

        } catch (FileNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }


    /**
     * @param workbook
     * @param sheet
     * @param fileUrl
     * @param fileType
     * @param row
     * @param col      void
     * @Title: picture
     * @Description: 方法二：图片插入至坐标
     * @author: zxfeng
     * @date 2021年3月8日 下午5:37:33
     * @version
     */
    public static void picture(XSSFWorkbook workbook, XSSFSheet sheet, String fileUrl, String fileType, int row, int col) {
        try {
            XSSFDrawing patriarch = sheet.createDrawingPatriarch();
            // 构造URL
            URL url = new URL(fileUrl);
            // 打开连接
            URLConnection con = url.openConnection();
            //设置请求超时为5s
            con.setConnectTimeout(8 * 1000);
            // 输入流
            InputStream is = con.getInputStream();
            ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
            BufferedImage bufferImg = ImageIO.read(is);
            ImageIO.write(bufferImg, "JPEG", byteArrayOut);
            bufferImg.flush();
            byteArrayOut.flush();
            XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 255, 255, (short) 0, 0, (short) 5, 8);
            patriarch.createPicture(anchor, workbook.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));

            OutputStream fileOut = new FileOutputStream("D:\\temp\\ExcelImageWriter.xlsx");
            workbook.write(fileOut);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    public static void picture3() throws IOException {
        // 创建Workbook对象
        Workbook workbook = new XSSFWorkbook();

// 创建Sheet对象
        Sheet sheet = workbook.createSheet("Sheet1");

// 创建Drawing对象
        Drawing<?> drawing = sheet.createDrawingPatriarch();

// 创建锚点
//        ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 0, 0, 0);

// 读取图片文件
//        File imageFile = new File("D:\\image\\OIP-A.jpg");
        File imageFile = new File("D:\\image\\25-1Z6121F9340-L.png");
        InputStream inputStream = null;
        try {
            inputStream = new FileInputStream(imageFile);
        } catch (FileNotFoundException e) {
            throw new RuntimeException(e);
        }


        // 构造URL
//        String fileUrl = "https://cn.bing.com/images/search?view=detailV2&ccid=DSO2qaoz&id=553AB21BB3A03AB84DE8DBF3C93E9ADF5ADA2396&thid=OIP.DSO2qaozX0tibWbN73gb5gHaE_&mediaurl=https%3a%2f%2fimg.phb123.com%2fuploads%2fallimg%2f190612%2f25-1Z6121F9340-L.png&exph=465&expw=690&q=%e5%9b%be%e7%89%87+%e5%8f%af%e7%88%b1&simid=607994149340651037&FORM=IRPRST&ck=5977A4D276D9027E4E05F3744E05FB23&selectedIndex=67";
//        String fileUrl = "https://guliamllecho.oss-cn-beijing.aliyuncs.com/uploadxlspic/2023-10-10/c729f143eb6d35bb748b22e669a08074.jpeg";
        String fileUrl = "https://ts3.cn.mm.bing.net/th?id=OIP-C.ZkoPhpKfJwsvGmpm8RsragHaFp&w=286&h=218&c=8&rs=1&qlt=90&o=6&pid=3.1&rm=2";
        URL url = new URL(fileUrl);
        // 打开连接
        URLConnection con = url.openConnection();
        //设置请求超时为5s
        con.setConnectTimeout(8 * 1000);
        // 输入流
        inputStream = con.getInputStream();

        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();

        byte[] buffer = new byte[4096];
        int bytesRead;

        while ((bytesRead = inputStream.read(buffer)) != -1) {
            outputStream.write(buffer, 0, bytesRead);
        }

        byte[] byteArray = outputStream.toByteArray();

//// 创建图片对象
//        int pictureIndex = workbook.addPicture(byteArray, Workbook.PICTURE_TYPE_JPEG);
////        int pictureIndex = workbook.addPicture(byteArray, Workbook.PICTURE_TYPE_PNG);
//        Picture picture = drawing.createPicture(anchor, pictureIndex);

// 设置图片大小
//        picture.resize();

//// 创建单元格对象
//        Row row = sheet.createRow(1);
//        Cell cell = row.createCell(0);

// 设置单元格的值为图片
//        cell.setCellValue("");

        // 创建锚点
//        ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 3, 1, 4, 2);
//        ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0,  3, 1, 4, 2);
        ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 0, 0, 0);
//        ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0,  1, 3 ,1, 5);

// 创建图片对象
        int pictureIndex = workbook.addPicture(byteArray, Workbook.PICTURE_TYPE_JPEG);
        Picture picture = drawing.createPicture(anchor, pictureIndex);

// 设置图片大小和位置
        picture.resize(); // 将图片调整为默认大小的1倍
//        picture.resize(200, 100); // 将图片调整为默认大小的1倍

// 调整图片在单元格内的位置
//        picture.getPreferredSize(); // 触发计算图片的尺寸
//        int pictureWidth = picture.getImageDimension().width;
//        int pictureHeight = picture.getImageDimension().height;
//        int cellWidth = sheet.getColumnWidth(3);
//        int cellHeight = sheet.getRow(1).getHeight();
//        int dx = (cellWidth - pictureWidth) / 2; // 在水平方向上使图片居中
//        int dy = (cellHeight - pictureHeight) / 2; // 在垂直方向上使图片居中
//        anchor.setDx1(dx * Units.EMU_PER_PIXEL);
//        anchor.setDy1(dy * Units.EMU_PER_PIXEL);
//        anchor.setDx2((dx + pictureWidth) * Units.EMU_PER_PIXEL);
//        anchor.setDy2((dy + pictureHeight) * Units.EMU_PER_PIXEL);

//// 设置单元格的值为图片
//        Cell cell = row.createCell(3);
//        cell.setCellValue("");


// 保存Excel文件
        FileOutputStream fileOut = new FileOutputStream("D:\\temp\\output.xlsx");
        workbook.write(fileOut);
        fileOut.close();

// 关闭输入流
        inputStream.close();
    }

    public static void picture4() throws IOException {
        // 创建Workbook对象
        Workbook workbook = new XSSFWorkbook();

// 创建Sheet对象
        Sheet sheet = workbook.createSheet("Sheet1");

// 创建Drawing对象
        Drawing<?> drawing = sheet.createDrawingPatriarch();

// 读取图片文件
        InputStream inputStream = null;

        String fileUrl = "https://ts3.cn.mm.bing.net/th?id=OIP-C.ZkoPhpKfJwsvGmpm8RsragHaFp&w=286&h=218&c=8&rs=1&qlt=90&o=6&pid=3.1&rm=2";
        URL url = new URL(fileUrl);
        // 打开连接
        URLConnection con = url.openConnection();
        //设置请求超时为5s
        con.setConnectTimeout(8 * 1000);
        // 输入流
        inputStream = con.getInputStream();

        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();

        byte[] buffer = new byte[4096];
        int bytesRead;

        while ((bytesRead = inputStream.read(buffer)) != -1) {
            outputStream.write(buffer, 0, bytesRead);
        }

        byte[] byteArray = outputStream.toByteArray();

        // 创建锚点
        ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 0, 1, 1);

// 创建图片对象
        int pictureIndex = workbook.addPicture(byteArray, Workbook.PICTURE_TYPE_JPEG);
        Picture picture = drawing.createPicture(anchor, pictureIndex);

// 设置图片大小和位置
        picture.resize(); // 将图片调整为默认大小的1倍

// 保存Excel文件
        FileOutputStream fileOut = new FileOutputStream("D:\\temp\\output.xlsx");
        workbook.write(fileOut);
        fileOut.close();

// 关闭输入流
        inputStream.close();
    }

}
